<?php

include('lib/UIS_Reader.class.php');

function uissync_form ($form, &$form_state) {  
    $options = array(
        'members' => array('title' => t('Members')),
        'functions' => array('title' => t('Functions')),
        'functions_members' => array('title' => t('Members of functions')),
        'services_members' => array('title' => t('Members of services')),
        'link' => array('title' => t('Link UIS users with Drupal users')),
        'roles_functions' => array('title' => t('Add roles to function members')),
        'roles_services' => array('title' => t('Add roles to service members')),
    );
    
    $form['sync'] = array (
        '#type' => 'tableselect',
        '#title' => 'Sycnronise',
        '#header' => array('title' => t('To sync')),
        '#options' => $options,
        '#multiple' => true
      );
    
    
    $form['submit'] = array(
	'#type'	 => 'submit',
	'#value' => t('Synchronise now')
    );
    
    return $form;
}

function uissync_form_submit($form, &$form_state){
  
  // Set up the Batch API
  $batch = array('operations' => array(),
    'finished' => '',
    'title' => t('Syncing files'),
    'init_message' => t('Starting'),
    'progress_message' => t('@current out of @total synced'),
    'error_message' => t('An error occurred and some or all of the syncs have failed.'),
    'file' => drupal_get_path('module', 'uissync') . '/uissync.sync.inc',
  );
  
  
  if($form_state['values']['sync']['members']){
    $batch['operations'][] = array('uissync_save_leden', array());
  }
  
  if($form_state['values']['sync']['functions']){
    $batch['operations'][] = array('uissync_save_functions', array());
  }
  
  if($form_state['values']['sync']['functions_members']){
    $batch['operations'][] = array('uissync_save_function_leden', array());
  }
  
  if($form_state['values']['sync']['services_members']){
    $batch['operations'][] = array('uissync_save_service_leden', array());
  }
  
  if($form_state['values']['sync']['link']){
    $batch['operations'][] = array('uissync_link_leden', array());
  }
  
  if($form_state['values']['sync']['roles_functions']){
    $batch['operations'][] = array('uissync_apply_roles_functies', array());
  }
  
  if($form_state['values']['sync']['roles_services']){
    $batch['operations'][] = array('uissync_apply_roles_services', array());
  }
  
  //start the batch
  batch_set($batch);
  
  $form_state['redirect'] = 'admin/config/people/uissync';
    
}

/**
 * lees alle leden in en steek ze in de db
 * @return type 
 */
function uissync_save_leden(){
    $usersFields = array(	'werkjaar', 
				'lidnr', 
				'naam',
				'voornaam',
				'afk',
				'straat',
				'geslacht',
				'geboortedatum',
				'geboorteplaats',
				'postcode',
				'plaats',
				'land',
				'medewerker',
				'kernvergadering',
				'uranialid',
				'oberon',
				'actief',
				'overleden',
				'website',
				'info',
				'gsm',
				'telefoon',
				'fax',
				'email',
				'ontvang_digitaal');
    
    $file = drupal_realpath('private://uis/qry-web1.txt');
    
    $oFile = new UIS_FileReader($file, $usersFields);
    
    $fieldsToSave = array(	'werkjaar', 
				'lidnr', 
				'naam',
				'voornaam',
				'afk',
				'straat',
				'geslacht',
				'geboortedatum',
				'geboorteplaats',
				'postcode',
				'plaats',
				'land',
				'actief',
				'overleden',
				'website',
				'info',
				'gsm',
				'telefoon',
				'fax',
				'email',
				'ontvang_digitaal');
    
    $convert['geslacht'] = function($value){
	return (strtolower($value) == 'v') ? 1 : 0;
    };
    
    $convert['actief'] = function($value){
	return (strtolower($value) == 'Yes') ? 1 : 0;
    };
    
    $convert['land'] = function($value){
	return htmlentities($value);
    };
    
    $convert['naam'] = function($value){
	return htmlentities($value);
    };
    
    $convert['voornaam'] = function($value){
	return htmlentities($value);
    };
    
    $convert['straat'] = function($value){
	return htmlentities($value);
    };

    $convert['geboortedatum'] = function($value){
	preg_match_all("/[0-9]{2,4}/", $value, $matches, PREG_PATTERN_ORDER);
	if(count($matches[0]) == 6){
	    return mktime(0, 0, 0, $matches[0][1], $matches[0][0], $matches[0][2]);
	}
	return 0;
    };
    
    $convert['postcode'] = function($value){
	return filter_var($value, FILTER_SANITIZE_NUMBER_INT);
    };
    
    
    $users = $oFile->getAll($fieldsToSave, $convert);
    
    try {
        
	foreach($users as $user){
            db_merge('uis_leden')->key(array('lidnr' => $user['lidnr']))->fields($user)->execute();
	}

	drupal_set_message(t('Users are syncronised'));
    
    } catch (PDOException $e) {
	drupal_set_message(t('Unable to sync users ') . $e->getMessage(), 'error');
    }
}

/**
 * lees alle functies in en steek ze in de db
 * @return type 
 */
function uissync_save_functions(){
    $fields = array(	'fid', 
			'naam', 
			'verantwoordelijke');
    
    $file = drupal_realpath('private://uis/qry-webFunctieOmschrijvingen.txt');
    
    $oFile = new UIS_FileReader($file, $fields);
    
    $convert['naam'] = function($value){
      return htmlentities($value);
    };
    
    $fileFunctions = $oFile->getAll($fields, $convert);
    
    try {
      db_truncate('uis_functies')->execute();

      $insert = db_insert('uis_functies')->fields(array('fid', 'naam', 'verantwoordelijke'));
      $execute = false;

      foreach ($fileFunctions as $function) {
        
        $insert->values($function);
        $execute = true;
      }


      if ($execute) {
        $insert->execute();
      }

      drupal_set_message(t('Functions are syncronised'));
    
    } catch (PDOException $e) {
      drupal_set_message(t('Unable to sync functions ') . $e->getMessage(), 'error');
    }
}

/**
 * lees de koppeling tussen leden en functies in en steek ze in de db
 * als een id dubbel voor komt, word deze genegeerd
 */
function uissync_save_function_leden(){

    $fields = array(	'lidnr', 
			'fid');
    
    $file = drupal_realpath('private://uis/qry-webFuncties.txt');
    
    $oFile = new UIS_FileReader($file, $fields);
    
    $functions = $oFile->getAll();
    
    try {
	
	db_truncate('uis_leden_functies')->execute();
	
	foreach($functions as $function){
	    db_insert('uis_leden_functies')->fields($function)->execute();
	}
	
	drupal_set_message(t('Members of the functions are syncronised'));
    
    } catch (PDOException $e) {
	drupal_set_message(t('Unable to sync members of the functions ') . $e->getMessage(), 'error');
    }

}

/**
 * lees de koppeling tussen service en leden in en steek ze in de db
 * dubbele id's worden genegeerd
 */
function uissync_save_service_leden(){

    $fields = array(	'lidnr', 
			'service_id',
			'werkjaar');
    
    $file = drupal_realpath('private://uis/qry-web2.txt');
    
    $oFile = new UIS_FileReader($file, $fields);
    
    $members = $oFile->getAll();
    
    try {
	
	db_truncate('uis_leden_service')->execute();
	
	foreach($members as $member){
	    db_merge('uis_leden_service')->key($member)->insertFields($member)->execute();
	}
	
	drupal_set_message(t('Members of the services are syncronised'));
    
    } catch (PDOException $e) {
	drupal_set_message(t('Unable to sync members of the services ') . $e->getMessage(), 'error');
    }

}

/**
 * get an array of all the users who are found in the system to syncronise
 * @return array of all the users to sync 
 */
function uissync_link_leden(){
  $exit = false;
  
  $abbrName = variable_get('uissync_abbreviation');
  $nrName   = variable_get('uissync_member_number', 0);
  
  if (empty($abbrName)) {
    drupal_set_message(t('Abbreviation field is not set in the settings.'), 'error');
    $exit = true;
  }
  
  if (empty($nrName)) {
    drupal_set_message(t('Member number field is not set in the settings.'), 'error');
    $exit = true;
  }
  
  if ($exit) {
    return;
  }

  $tableAbbr = 'field_data_' . $abbrName;
  $tableNr = 'field_data_' . $nrName;
  
  $fieldAbbr = $abbrName . '_value';
  $fieldNr = $nrName . '_value';

  try {
  
    $query = "SELECT  u.uid, $fieldAbbr AS 'abbr', $fieldNr AS 'nr'
              FROM users u
              INNER JOIN {profile} p
                  ON(u.uid = p.uid)
              INNER JOIN {" . $tableAbbr . "} afk
                  ON(p.pid = afk.entity_id)
              INNER JOIN {" . $tableNr . "} nr
                  ON(p.pid = nr.entity_id)";

    $resultUsers = db_query($query);

    // maak een array op basis van de afkoring om te kunne zoeken
    $drupalUsers = array();
    foreach($resultUsers->fetchAll() as $row){
      $drupalUsers[strtolower($row->abbr)] = $row;
    }

    $uisLeden = db_query('SELECT * FROM {uis_leden};');

    db_truncate('uis_leden_users')->execute();

    $insert = db_insert('uis_leden_users')->fields(array('uid', 'lidnr'));

    // overloop alle leden en check of het lidnummer gevonden is
    foreach ($uisLeden->fetchAll() as $row) {

      $abbr = strtolower($row->afk);

      if (key_exists($abbr, $drupalUsers)) {

        // controleren het lidnummer voor de zekerheid
        if ($row->lidnr == $drupalUsers[$abbr]->nr) {
          //gebruiker gevonden

          $insert->values(array('uid' => $drupalUsers[$abbr]->uid, 'lidnr' => $drupalUsers[$abbr]->nr));

        }
      }
    }

    $insert->execute();

    drupal_set_message(t('UIS members are linked to the Drupal users'));
    
  } catch (PDOException $e) {
      drupal_set_message(t('Unable link UIS users to Drupal users ') . $e->getMessage(), 'error');
  }
}

function uissync_apply_roles_functies(){
  
  try {
  
    db_query('DELETE
              FROM {users_roles}
              WHERE
              rid IN (SELECT DISTINCT rid FROM {uis_functies_roles})
              AND uid IN (SELECT uid FROM {uis_leden_users})');


    $roles = db_select('uis_functies_roles', 'r')->fields('r')->execute();

    foreach ($roles as $row) {

      $leden = db_query('SELECT uid FROM {uis_leden_functies} f
                          INNER JOIN {uis_leden_users} u
                          ON (f.lidnr = u.lidnr)
                          WHERE f.fid = :fid;', array(':fid' => $row->fid));

      $insert = db_insert('users_roles')->fields(array('uid', 'rid'));

      foreach ($leden as $lid) {
        $insert->values( array('uid' => $lid->uid, 'rid' => $row->rid) );
      }

      $insert->execute();

    }
    
    drupal_set_message(t('Roles for functions added'));
    
  } catch (PDOException $e) {
      drupal_set_message(t('Unable to add roles of the functions to users ') . $e->getMessage(), 'error');
  }
}

function uissync_apply_roles_services(){
  
  try {
    
    db_query('DELETE
              FROM {users_roles}
              WHERE
              rid IN (SELECT DISTINCT rid FROM {uis_services_roles})
              AND uid IN (SELECT uid FROM {uis_leden_users})');


    $roles = db_select('uis_services_roles', 'r')->fields('r')->execute();


    foreach ($roles as $row) {

      $leden = db_query('SELECT DISTINCT uid FROM {uis_leden_service} s
                          INNER JOIN {uis_leden_users} u
                          ON (s.lidnr = u.lidnr)
                          WHERE  werkjaar IN (YEAR(now()), YEAR(now())+1) AND s.service_id = :sid;', array(':sid' => $row->sid));
		

      $insert = db_insert('users_roles')->fields(array('uid', 'rid'));

      foreach ($leden as $lid) {
        $insert->values( array('uid' => $lid->uid, 'rid' => $row->rid) );
      }

      $insert->execute();
    }
    
    drupal_set_message(t('Roles for services added'));
    
  } catch (PDOException $e) {
      drupal_set_message(t('Unable to add roles of the services to users <br>') . $e->getMessage(), 'error');
  }
}